package com.examiner.dao;

import com.examiner.bean.Department;
import com.examiner.bean.Job;
import com.examiner.bean.test.NewTestUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface NewTestUserDao extends JpaRepository<NewTestUser,Integer>, JpaSpecificationExecutor<NewTestUser> {
    List<NewTestUser> findAllByNid(Integer id);

    @Query(value = "delete from new_test_user where nid=?1", nativeQuery = true)
    @Modifying(clearAutomatically = true)
    void deleteAllByNid(Integer id);

    @Query(value = "select * from new_test_user where uid=?1 limit ?2,?3", nativeQuery = true)
    List<NewTestUser> findMyTestList(Integer id, int i, int pageSize, String query);

    @Query(value = "select count(id) from new_test_user where uid=?1", nativeQuery = true)
    int findMyListTotal(Integer id, String query);


    @Query(value = "select * from new_test_user where eid=?1 and (user_name like %?4% ) limit ?2,?3", nativeQuery = true)
    List<NewTestUser> findCheckTestList(Integer id, int i, int pageSize, String query);

    @Query(value = "select count(id) from new_test_user where eid=?1 and (user_name like %?2% ) ", nativeQuery = true)
    int findCheckTestListTotal(Integer id, String query);


    @Query(value = "select * from new_test_user " +
                        "where ((coalesce(?3, null) is null or (sid in (?3) and stype='职位')) " +
                            "or (coalesce(?4, null) is null or (sid in (?4) and stype='部门'))) " +
                            "and ((coalesce(?5, null) is null or (sid in (?5) and stype='职位')) " +
                            "or (coalesce(?6, null) is null or (sid in (?6) and stype='部门')) " +
                            "or (coalesce(?7, null) is null or tid in (?7)) " +
                            "or (coalesce(?8, null) is null or uid in (?8))) limit ?1,?2", nativeQuery = true)
    List<NewTestUser> findSelectList(int i, int pageSize, List<Integer> allLowerJob, List<Integer> allLowerDepartment,
                                     List<Integer> jobByQuery, List<Integer> departmentByQuery, List<Integer> testDBByQuery, List<Integer> personByQuery);

    @Query(value = "select count(id) from new_test_user " +
            "where ((coalesce(?1,null) is null or (sid in (?1) and stype='职位')) " +
            "or (coalesce(?2,null) is null or (sid in (?2) and stype='部门'))) " +
            "and ((coalesce(?3,null) is null or (sid in (?3) and stype='职位')) " +
            "or (coalesce(?4,null) is null or (sid in (?4) and stype='部门')) " +
            "or (coalesce(?5,null) is null or tid in (?5)) " +
            "or (coalesce(?6,null) is null or uid in (?6))) ", nativeQuery = true)
    int findSelectListTotal(List<Integer> allLowerJob, List<Integer> allLowerDepartment,
                                     List<Integer> jobByQuery, List<Integer> departmentByQuery, List<Integer> testDBByQuery, List<Integer> personByQuery);
}
